﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapBSFARB(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveBSFARB(DataRow dwData, DataRow dwDispo)
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryBSFARB(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapBSFARB(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertBSFARB(dwLocal);
            }
            UpdateBSFARB(dwLocal);
        }
        public void InsertBSFARB(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO BSFARB(BSFMANDANT, BSFKART, BSFKVAR, BSFKFARBE, BSFKZKETTF, BSFSTATUS, BSFSFKZ, BSFBEZ, BSFTEXTSCHL, BSFFKARTNR, BSFROHFARBE, BSFMUDESS, BSFUNIFARB, BSFZTWAHL, BSFRUESTGRP, BSFPRUEFGRP, BSFFDVF, BSFPRGRP, BSFGRPBEZ, BSFPREIS, BSFZUABPR, BSFZUABAB, BSFNFART, BSFWERT, BSFKZPA, BSFZUABVH, BSFZUABAK, BSFZUABKU, BSFZUABPROZ, BSFFA, BSFGA, BSFQA, BSFQPKTH, BSFQPKTLH, BSFLOAN, BSFLBAN, BSFLPAN, BSFABKZAN, BSFPKL, BSFVBBRT, BSFBRTTOLPL, BSFBRTTOLMI, BSFVBGRQM, BSFVBGRLFD, BSFGWTOLPL, BSFGWTOLMIN, BSFLAEAEND, BSFGWVERL, BSFRESTSCHL, BSFPHWERTMI, BSFPHWERTMA, BSFWEISSGRD, BSFNETZFVON, BSFNETZFBIS, BSFNETZFSEK, BSFRSSFTRKV, BSFRSSFTRKB, BSFUNIFAEH, BSFFADGERAD, BSFMUSTFRB, BSFVORB, BSFNACHB, BSFMU, BSFMURLOD, BSFMUPROD, BSFMUCTOD, BSFMUDTRL, BSFMUBIRL, BSFLMANRKRS, BSFLMALFDNR, BSFLMAPOS, BSFLODKRS, BSFLODLFD, BSFLODPOS, BSFFILLER, BSFAENZL, BSFAENB, BSFAEND, BSFNEUB, BSFNEUD)"
                + "VALUES ("
            + "'" + dwData["BSFMANDANT"].ToString() + "', '" + dwData["BSFKART"].ToString() + "','" + dwData["BSFKVAR"].ToString() + "','" + dwData["BSFKFARBE"].ToString() + "', ' ', 'A', ' ','" + dwData["BSFBEZ"].ToString() + "', '          ', '          ', '          ', '          ', '          ', 0, '   ', ' ', '   '"
            + ", ' ', '                    ', 0, 0, 0, '    ', 0, ' ', 0, 0, 0, ' ', 0, 0, 0, 0, 0, '  ', '  ', '        ', ' ', '  ', 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', '          '"
            + ", '   ', '   ', ' ', '00', '00', ' ', 0, '        ', '  ', '0000', '00', '  ', '000000', '000', '                                                              '"
            + ", 1, '        ', 0,'" + dwData["BSFNEUB"].ToString() + "'," + dwData["BSFNEUD"].ToString() + ")";
            /*
            or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwData["BSFMANDANT"].ToString();
            or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwData["BSFKART"].ToString();
            or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwData["BSFKVAR"].ToString();
            //or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = dwData["BSFKFARBE"].ToString();
            //or1.Parameters.Add("BSFBEZ", OracleType.NVarChar).Value = dwData["BSFBEZ"].ToString();
            or1.Parameters.Add("BSFNEUB", OracleType.NVarChar).Value = dwData["BSFNEUB"].ToString();
            or1.Parameters.Add("BSFNEUD", OracleType.Number).Value = dwData["BSFNEUD"].ToString();*/
            setResult = Connect2Proxy(or1);
        }
        public void DeleteBSFARB(string client, string art, string var, string color)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE BSFARB"
           + " WHERE BSFMANDANT=:BSFMANDANT AND BSFKART=:BSFKART AND BSFKVAR=:BSFKVAR AND BSFKFARBE=:BSFKFARBE";
            or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = client;
            or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = var;
            or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = color;
            Connect2Proxy(or1);
        }
        public void UpdateBSFARB(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE BSFARB SET "
                 + "BSFMANDANT=:BSFMANDANT,"
                + "BSFKART=:BSFKART,"
                + "BSFKVAR=:BSFKVAR,"
                + "BSFKFARBE=:BSFKFARBE,"
                + "BSFKZKETTF=:BSFKZKETTF,"
                + "BSFSTATUS=:BSFSTATUS,"
                + "BSFSFKZ=:BSFSFKZ,"
                + "BSFBEZ=:BSFBEZ,"
                + "BSFTEXTSCHL=:BSFTEXTSCHL,"
                + "BSFFKARTNR=:BSFFKARTNR,"
                + "BSFROHFARBE=:BSFROHFARBE,"
                + "BSFMUDESS=:BSFMUDESS,"
                + "BSFUNIFARB=:BSFUNIFARB,"
                + "BSFZTWAHL=:BSFZTWAHL,"
                + "BSFRUESTGRP=:BSFRUESTGRP,"
                + "BSFPRUEFGRP=:BSFPRUEFGRP,"
                + "BSFFDVF=:BSFFDVF,"
                + "BSFPRGRP=:BSFPRGRP,"
                + "BSFGRPBEZ=:BSFGRPBEZ,"
                + "BSFPREIS=:BSFPREIS,"
                + "BSFZUABPR=:BSFZUABPR,"
                + "BSFZUABAB=:BSFZUABAB,"
                + "BSFNFART=:BSFNFART,"
                + "BSFWERT=:BSFWERT,"
                + "BSFKZPA=:BSFKZPA,"
                + "BSFZUABVH=:BSFZUABVH,"
                + "BSFZUABAK=:BSFZUABAK,"
                + "BSFZUABKU=:BSFZUABKU,"
                + "BSFZUABPROZ=:BSFZUABPROZ,"
                + "BSFFA=:BSFFA,"
                + "BSFGA=:BSFGA,"
                + "BSFQA=:BSFQA,"
                + "BSFQPKTH=:BSFQPKTH,"
                + "BSFQPKTLH=:BSFQPKTLH,"
                + "BSFLOAN=:BSFLOAN,"
                + "BSFLBAN=:BSFLBAN,"
                + "BSFLPAN=:BSFLPAN,"
                + "BSFABKZAN=:BSFABKZAN,"
                + "BSFPKL=:BSFPKL,"
                + "BSFVBBRT=:BSFVBBRT,"
                + "BSFBRTTOLPL=:BSFBRTTOLPL,"
                + "BSFBRTTOLMI=:BSFBRTTOLMI,"
                + "BSFVBGRQM=:BSFVBGRQM,"
                + "BSFVBGRLFD=:BSFVBGRLFD,"
                + "BSFGWTOLPL=:BSFGWTOLPL,"
                + "BSFGWTOLMIN=:BSFGWTOLMIN,"
                + "BSFLAEAEND=:BSFLAEAEND,"
                + "BSFGWVERL=:BSFGWVERL,"
                + "BSFRESTSCHL=:BSFRESTSCHL,"
                + "BSFPHWERTMI=:BSFPHWERTMI,"
                + "BSFPHWERTMA=:BSFPHWERTMA,"
                + "BSFWEISSGRD=:BSFWEISSGRD,"
                + "BSFNETZFVON=:BSFNETZFVON,"
                + "BSFNETZFBIS=:BSFNETZFBIS,"
                + "BSFNETZFSEK=:BSFNETZFSEK,"
                + "BSFRSSFTRKV=:BSFRSSFTRKV,"
                + "BSFRSSFTRKB=:BSFRSSFTRKB,"
                + "BSFUNIFAEH=:BSFUNIFAEH,"
                + "BSFFADGERAD=:BSFFADGERAD,"
                + "BSFMUSTFRB=:BSFMUSTFRB,"
                + "BSFVORB=:BSFVORB,"
                + "BSFNACHB=:BSFNACHB,"
                + "BSFMU=:BSFMU,"
                + "BSFMURLOD=:BSFMURLOD,"
                + "BSFMUPROD=:BSFMUPROD,"
                + "BSFMUCTOD=:BSFMUCTOD,"
                + "BSFMUDTRL=:BSFMUDTRL,"
                + "BSFMUBIRL=:BSFMUBIRL,"
                + "BSFLMANRKRS=:BSFLMANRKRS,"
                + "BSFLMALFDNR=:BSFLMALFDNR,"
                + "BSFLMAPOS=:BSFLMAPOS,"
                + "BSFLODKRS=:BSFLODKRS,"
                + "BSFLODLFD=:BSFLODLFD,"
                + "BSFLODPOS=:BSFLODPOS,"
                + "BSFFILLER=:BSFFILLER,"
                + "BSFAENZL=:BSFAENZL,"
                + "BSFAENB=:BSFAENB,"
                + "BSFAEND=:BSFAEND,"
                + "BSFNEUB=:BSFNEUB,"
                + "BSFNEUD=:BSFNEUD "
                + " WHERE  "
                + "  BSFMANDANT=:BSFMANDANT AND BSFKART=:BSFKART  AND BSFKVAR=:BSFKVAR  AND BSFKFARBE=:BSFKFARBE";
            or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwData["BSFMANDANT"].ToString();
            or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwData["BSFKART"].ToString();
            or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwData["BSFKVAR"].ToString();
            or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = dwData["BSFKFARBE"].ToString();
            or1.Parameters.Add("BSFKZKETTF", OracleType.NVarChar).Value = dwData["BSFKZKETTF"].ToString();
            or1.Parameters.Add("BSFSTATUS", OracleType.NVarChar).Value = dwData["BSFSTATUS"].ToString();
            or1.Parameters.Add("BSFSFKZ", OracleType.NVarChar).Value = dwData["BSFSFKZ"].ToString();
            or1.Parameters.Add("BSFBEZ", OracleType.NVarChar).Value = dwData["BSFBEZ"].ToString();
            or1.Parameters.Add("BSFTEXTSCHL", OracleType.NVarChar).Value = dwData["BSFTEXTSCHL"].ToString();
            or1.Parameters.Add("BSFFKARTNR", OracleType.NVarChar).Value = dwData["BSFFKARTNR"].ToString();
            or1.Parameters.Add("BSFROHFARBE", OracleType.NVarChar).Value = dwData["BSFROHFARBE"].ToString();
            or1.Parameters.Add("BSFMUDESS", OracleType.NVarChar).Value = dwData["BSFMUDESS"].ToString();
            or1.Parameters.Add("BSFUNIFARB", OracleType.NVarChar).Value = dwData["BSFUNIFARB"].ToString();
            or1.Parameters.Add("BSFZTWAHL", OracleType.NVarChar).Value = dwData["BSFZTWAHL"].ToString();
            or1.Parameters.Add("BSFRUESTGRP", OracleType.NVarChar).Value = dwData["BSFRUESTGRP"].ToString();
            or1.Parameters.Add("BSFPRUEFGRP", OracleType.NVarChar).Value = dwData["BSFPRUEFGRP"].ToString();
            or1.Parameters.Add("BSFFDVF", OracleType.NVarChar).Value = dwData["BSFFDVF"].ToString();
            or1.Parameters.Add("BSFPRGRP", OracleType.NVarChar).Value = dwData["BSFPRGRP"].ToString();
            or1.Parameters.Add("BSFGRPBEZ", OracleType.NVarChar).Value = dwData["BSFGRPBEZ"].ToString();
            or1.Parameters.Add("BSFPREIS", OracleType.NVarChar).Value = dwData["BSFPREIS"].ToString();
            or1.Parameters.Add("BSFZUABPR", OracleType.NVarChar).Value = dwData["BSFZUABPR"].ToString();
            or1.Parameters.Add("BSFZUABAB", OracleType.NVarChar).Value = dwData["BSFZUABAB"].ToString();
            or1.Parameters.Add("BSFNFART", OracleType.NVarChar).Value = dwData["BSFNFART"].ToString();
            or1.Parameters.Add("BSFWERT", OracleType.NVarChar).Value = dwData["BSFWERT"].ToString();
            or1.Parameters.Add("BSFKZPA", OracleType.NVarChar).Value = dwData["BSFKZPA"].ToString();
            or1.Parameters.Add("BSFZUABVH", OracleType.NVarChar).Value = dwData["BSFZUABVH"].ToString();
            or1.Parameters.Add("BSFZUABAK", OracleType.NVarChar).Value = dwData["BSFZUABAK"].ToString();
            or1.Parameters.Add("BSFZUABKU", OracleType.NVarChar).Value = dwData["BSFZUABKU"].ToString();
            or1.Parameters.Add("BSFZUABPROZ", OracleType.NVarChar).Value = dwData["BSFZUABPROZ"].ToString();
            or1.Parameters.Add("BSFFA", OracleType.NVarChar).Value = dwData["BSFFA"].ToString();
            or1.Parameters.Add("BSFGA", OracleType.NVarChar).Value = dwData["BSFGA"].ToString();
            or1.Parameters.Add("BSFQA", OracleType.NVarChar).Value = dwData["BSFQA"].ToString();
            or1.Parameters.Add("BSFQPKTH", OracleType.NVarChar).Value = dwData["BSFQPKTH"].ToString();
            or1.Parameters.Add("BSFQPKTLH", OracleType.NVarChar).Value = dwData["BSFQPKTLH"].ToString();
            or1.Parameters.Add("BSFLOAN", OracleType.NVarChar).Value = dwData["BSFLOAN"].ToString();
            or1.Parameters.Add("BSFLBAN", OracleType.NVarChar).Value = dwData["BSFLBAN"].ToString();
            or1.Parameters.Add("BSFLPAN", OracleType.NVarChar).Value = dwData["BSFLPAN"].ToString();
            or1.Parameters.Add("BSFABKZAN", OracleType.NVarChar).Value = dwData["BSFABKZAN"].ToString();
            or1.Parameters.Add("BSFPKL", OracleType.NVarChar).Value = dwData["BSFPKL"].ToString();
            or1.Parameters.Add("BSFVBBRT", OracleType.NVarChar).Value = dwData["BSFVBBRT"].ToString();
            or1.Parameters.Add("BSFBRTTOLPL", OracleType.NVarChar).Value = dwData["BSFBRTTOLPL"].ToString();
            or1.Parameters.Add("BSFBRTTOLMI", OracleType.NVarChar).Value = dwData["BSFBRTTOLMI"].ToString();
            or1.Parameters.Add("BSFVBGRQM", OracleType.NVarChar).Value = dwData["BSFVBGRQM"].ToString();
            or1.Parameters.Add("BSFVBGRLFD", OracleType.NVarChar).Value = dwData["BSFVBGRLFD"].ToString();
            or1.Parameters.Add("BSFGWTOLPL", OracleType.NVarChar).Value = dwData["BSFGWTOLPL"].ToString();
            or1.Parameters.Add("BSFGWTOLMIN", OracleType.NVarChar).Value = dwData["BSFGWTOLMIN"].ToString();
            or1.Parameters.Add("BSFLAEAEND", OracleType.NVarChar).Value = dwData["BSFLAEAEND"].ToString();
            or1.Parameters.Add("BSFGWVERL", OracleType.NVarChar).Value = dwData["BSFGWVERL"].ToString();
            or1.Parameters.Add("BSFRESTSCHL", OracleType.NVarChar).Value = dwData["BSFRESTSCHL"].ToString();
            or1.Parameters.Add("BSFPHWERTMI", OracleType.NVarChar).Value = dwData["BSFPHWERTMI"].ToString();
            or1.Parameters.Add("BSFPHWERTMA", OracleType.NVarChar).Value = dwData["BSFPHWERTMA"].ToString();
            or1.Parameters.Add("BSFWEISSGRD", OracleType.NVarChar).Value = dwData["BSFWEISSGRD"].ToString();
            or1.Parameters.Add("BSFNETZFVON", OracleType.NVarChar).Value = dwData["BSFNETZFVON"].ToString();
            or1.Parameters.Add("BSFNETZFBIS", OracleType.NVarChar).Value = dwData["BSFNETZFBIS"].ToString();
            or1.Parameters.Add("BSFNETZFSEK", OracleType.NVarChar).Value = dwData["BSFNETZFSEK"].ToString();
            or1.Parameters.Add("BSFRSSFTRKV", OracleType.NVarChar).Value = dwData["BSFRSSFTRKV"].ToString();
            or1.Parameters.Add("BSFRSSFTRKB", OracleType.NVarChar).Value = dwData["BSFRSSFTRKB"].ToString();
            or1.Parameters.Add("BSFUNIFAEH", OracleType.NVarChar).Value = dwData["BSFUNIFAEH"].ToString();
            or1.Parameters.Add("BSFFADGERAD", OracleType.NVarChar).Value = dwData["BSFFADGERAD"].ToString();
            or1.Parameters.Add("BSFMUSTFRB", OracleType.NVarChar).Value = dwData["BSFMUSTFRB"].ToString();
            or1.Parameters.Add("BSFVORB", OracleType.NVarChar).Value = dwData["BSFVORB"].ToString();
            or1.Parameters.Add("BSFNACHB", OracleType.NVarChar).Value = dwData["BSFNACHB"].ToString();
            or1.Parameters.Add("BSFMU", OracleType.NVarChar).Value = dwData["BSFMU"].ToString();
            or1.Parameters.Add("BSFMURLOD", OracleType.NVarChar).Value = dwData["BSFMURLOD"].ToString();
            or1.Parameters.Add("BSFMUPROD", OracleType.NVarChar).Value = dwData["BSFMUPROD"].ToString();
            or1.Parameters.Add("BSFMUCTOD", OracleType.NVarChar).Value = dwData["BSFMUCTOD"].ToString();
            or1.Parameters.Add("BSFMUDTRL", OracleType.NVarChar).Value = dwData["BSFMUDTRL"].ToString();
            or1.Parameters.Add("BSFMUBIRL", OracleType.NVarChar).Value = dwData["BSFMUBIRL"].ToString();
            or1.Parameters.Add("BSFLMANRKRS", OracleType.NVarChar).Value = dwData["BSFLMANRKRS"].ToString();
            or1.Parameters.Add("BSFLMALFDNR", OracleType.NVarChar).Value = dwData["BSFLMALFDNR"].ToString();
            or1.Parameters.Add("BSFLMAPOS", OracleType.NVarChar).Value = dwData["BSFLMAPOS"].ToString();
            or1.Parameters.Add("BSFLODKRS", OracleType.NVarChar).Value = dwData["BSFLODKRS"].ToString();
            or1.Parameters.Add("BSFLODLFD", OracleType.NVarChar).Value = dwData["BSFLODLFD"].ToString();
            or1.Parameters.Add("BSFLODPOS", OracleType.NVarChar).Value = dwData["BSFLODPOS"].ToString();
            or1.Parameters.Add("BSFFILLER", OracleType.NVarChar).Value = dwData["BSFFILLER"].ToString();
            or1.Parameters.Add("BSFAENZL", OracleType.NVarChar).Value = dwData["BSFAENZL"].ToString();
            or1.Parameters.Add("BSFAENB", OracleType.NVarChar).Value = dwData["BSFAENB"].ToString();
            or1.Parameters.Add("BSFAEND", OracleType.NVarChar).Value = dwData["BSFAEND"].ToString();
            or1.Parameters.Add("BSFNEUB", OracleType.NVarChar).Value = dwData["BSFNEUB"].ToString();
            or1.Parameters.Add("BSFNEUD", OracleType.NVarChar).Value = dwData["BSFNEUD"].ToString();
            Connect2Proxy(or1);
        }
        public DataSet QueryBSFARB(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM BSFARB WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM BSFARB"
                   + " WHERE BSFMANDANT=:BSFMANDANT AND BSFKART=:BSFKART AND BSFKVAR=:BSFKVAR AND BSFKFARBE=:BSFKFARBE";
                    or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwData["BSFMANDANT"].ToString();
                    or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwData["BSFKART"].ToString();
                    or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwData["BSFKVAR"].ToString();
                    or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = dwData["BSFKFARBE"].ToString();
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "BSFARB";
            }

            setReturn.Merge(Result);
            return setReturn;
        }

    }
}
